/**
 * 
 */
package com.java.test.paymanage.mapper;

import com.java.test.paymanage.dto.SkAccountInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Administrator
 *
 */
@Component
public class SkAccountMapper {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<SkAccountInfo> selectskaccount(SkAccountInfo info) {
		String sql = "select a.*,b.pay_type_name as payTypeName,'' foundsTypeName from paymanage_skaccount a left join paymanage_paytype b on a.pay_type=b.id where 1=1 ";

		if (info.getPayType() != 0) {
			sql += " and a.pay_type ='" + info.getPayType() + "'";
		}

		if (info.getChannelId() != 0) {
			sql += " and a.channel_id ='" + info.getChannelId() + "'";
		}

		if (info.getSliceId() != null && !"".equals(info.getSliceId())) {
			sql += " and a.slice_id ='" + info.getSliceId() + "'";
		}

		if (info.getSkAccount() != null && !"".equals(info.getSkAccount())) {
			sql += " and a.sk_account like '%" + info.getSkAccount() + "%'";
		}

		List<SkAccountInfo> list = jdbcTemplate.query(sql, new SkAccountInfo());

		return list;
	}

	public SkAccountInfo selectskaccountbyId(SkAccountInfo info) {
		String sql = "select a.*,b.pay_type_name as payTypeName,'' foundsTypeName from paymanage_skaccount a left join paymanage_paytype b on a.pay_type=b.id where a.id=?";

		SkAccountInfo infos = jdbcTemplate.queryForObject(sql, new SkAccountInfo(), info.getId());

		return infos;
	}

	public int selectcountbychannelid(int channelid) {

		String sql = "select count(*) from paymanage_skaccount where channel_id=?";

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class, channelid);

		return i.intValue();

	}

	public int insertskaccount(SkAccountInfo info) {

		String sql = "insert into paymanage_skaccount"
				+ "(pay_type,channel_name,sk_account,area_id,slice_id,yh_proportion,addpeople_name,add_time,status,money_type,sk_account_name,sort,channel_id)values"
				+ "(?,?,?,?,?,?,?,?,1,?,?,?,?)";

		int i = jdbcTemplate.update(sql, info.getPayType(), info.getChannelName(), info.getSkAccount(),
				info.getAreaId(), info.getSliceId(), info.getYhproportion(), info.getAddPeopleName(), info.getAddTime(),
				info.getMoneyTypeId(), info.getSkAccounName(), info.getSort(), info.getChannelId());

		return i;
	}

	public int deleteskaccount(SkAccountInfo info) {

		String sql = "delete from paymanage_skaccount where id='" + info.getId() + "'";

		int i = jdbcTemplate.update(sql);

		return i;
	}

	public int updateskaccount(SkAccountInfo info) {

		String sql = "update paymanage_skaccount set pay_type=?,channel_name=?,sk_account=?,area_id=?,"
				+ "slice_id=?,yh_proportion=?,money_type=?,sort=?,sk_account_name=?,channel_id=? where id=?";

		int i = jdbcTemplate.update(sql, info.getPayType(), info.getChannelName(), info.getSkAccount(),
				info.getAreaId(), info.getSliceId(), info.getYhproportion(), info.getMoneyTypeId(), info.getSort(),
				info.getSkAccounName(), info.getChannelId(), info.getId());

		return i;
	}

	public int updateskaccountsort(List<SkAccountInfo> info) {

		String sql = "update paymanage_skaccount set sort=? where id=?";

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return info.size();
				// 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
			}

			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, info.get(i).getSort());
				ps.setInt(2, info.get(i).getId());
			}
		});

		return 1;
	}

	/**
	 * @param info
	 * @return
	 */
	public int updateskaccountstatus(SkAccountInfo info) {

		String sql = "update paymanage_skaccount set status=? where id=?";

		int i = jdbcTemplate.update(sql, info.getStatus(), info.getId());

		return i;
	}

	/**
	 * @return
	 */
	public List<SkAccountInfo> selectallbankCard() {

		String sql = "select *  from paymanage_skaccount where status=1";

		List<SkAccountInfo> list = jdbcTemplate.query(sql, new RowMapper<SkAccountInfo>() {
			@Override
			public SkAccountInfo mapRow(ResultSet rs, int i) throws SQLException {
				SkAccountInfo s = new SkAccountInfo();
				s.setId(rs.getInt("id"));
				s.setSkAccount(rs.getString("sk_account"));
				s.setSkAccounName(rs.getString("sk_account_name"));
				return s;
			}
		});
		return list;
	}


	
	public List<SkAccountInfo> selectallskAccount() {

		String sql = "select *  from paymanage_skaccount";

		List<SkAccountInfo> list = jdbcTemplate.query(sql, new RowMapper<SkAccountInfo>() {
			@Override
			public SkAccountInfo mapRow(ResultSet rs, int i) throws SQLException {
				SkAccountInfo s = new SkAccountInfo();
				s.setId(rs.getInt("id"));
				s.setSkAccount(rs.getString("sk_account"));
				s.setSkAccounName(rs.getString("sk_account_name"));
				return s;
			}
		});
		return list;
	}
	
	public List<SkAccountInfo> selectSKAccount(String channelId) {
		StringBuffer sql = new StringBuffer("SELECT t.*,'' payTypeName, a.name foundsTypeName FROM paymanage_skaccount t " +
				"LEFT JOIN sys_funds_type a on t.money_type = a.id WHERE t.STATUS = 1");

		if (channelId!=null&&!"".equals(channelId)) {
			sql.append(" and t.channel_id = ?");
		}
		sql.append(" order by t.sort desc ");
		List<SkAccountInfo> list = jdbcTemplate.query(sql.toString(), new SkAccountInfo(),channelId);

		return list;
	}

}
